39
Building a Simple Parameter Form for a JSP-based Web Report

Figure 39-1 JSP Parameter Form

Text description of the illustration simplejsp_rpf.gif

Note: The JSP Parameter Form in this image enables the user to choose from a list of departments, connect to a database, then run a report based on the selected parameters.

Figure 39-2 JSP-based Web report based on a user parameter

Text description of the illustration simplejsp_fin.gif

Note: The JSP-based Web report in this image displays a graph and a tabular report based on the department selected in the JSP Parameter Form. To see how this report was built, refer to the Oracle Reports Tutorial.

Oracle Reports enables you to build reports where certain criteria are defined at runtime. To enable your users to define the criteria at runtime, you can use Parameter Forms. The steps in this chapter show you how to build an HTML Parameter Form using JavaServer Pages for a JSP-based Web report. To build a Parameter Form for a paper report, refer to the Reports Builder Online Help. This chapter shows you how to build a very simple Parameter Form. If this Parameter Form does not suit your needs, refer to a more advanced example located in the Getting Started with Oracle Reports Web site on the Oracle Technology Network (http://otn.oracle.com/products/reports/).

For conceptual information about JSPs and Parameter Forms for Web reports, refer to Section 2.2.1, "About JavaServer Pages (JSPs) and servlets" and Section 1.9.4, "About Parameter Forms for Web reports".

Example Scenario

Suppose you have an existing JSP-based Web report that shows a bar graph of employee salaries per department, as well as a tabular report that shows the employee details. Now, your customers want to be able to specify at runtime the employee information for a specific department, so that they don't have to read the data for all departments. The steps in this report will show you how to add a JSP Parameter Form to this Web report.

This example uses the resulting report from the Oracle Reports Tutorial. If you'd like to learn how to build the Web report that we use in this chapter, follow the steps in the tutorial.

Table 39-1 Features demonstrated in this example

Feature	Location
Use a text or HTML editor to create a simple HTML Parameter Form.	Section 39.2, "Create a Parameter Form in HTML"
Use the Data Model and Web Source views in Reports Builder to modify the Parameter Form and save as a JSP.	Section 39.3, "Modify the HTML Parameter Form in Reports Builder"
Use the Data Model view to modify the query for the target report to accept user parameters.	Section 39.4, "Set up the target report"
Test the deployment of the JSP Parameter Form and Web report.	Section 39.5, "Deploy the JSP report and Parameter Form"

39.1 Prerequisites for this example

To build the examples in this manual, you must have the example files we've provided, as well as access to the sample schema that is shipped with the Oracle9i database.

39.1.1 Example files

If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network (http://otn.oracle.com/products/reports) and install them on your machine.

To download and install the example files:

1. Go to the Oracle Technology Network Web site (http://otn.oracle.com/product/reports).

2. Click Getting Started with Oracle Reports.

3. Click Index, then browse the list of examples to find the "Building a Simple Parameter Form for a JSP-Based Web Report" example.

4. Download the file simplejsppf.zip into a temporary directory on your machine (e.g., d:\temp).

5. Unzip the contents of the file, maintaining the directory structure, into an examples directory on your machine (e.g., d:\orawin90\examples).

   This zip file contains the following files:

   Table 39-2 Files necessary for building a simple JSP Parameter Form

   File	Description
   Examples\SimpleJSPPF\source\paramform.html	An example HTML Parameter Form, which contains an example List of Values, a field, and a button.
   Examples\SimpleJSPPF\results\paramform.jsp	This JSP-based Web report contains the modifications to the Parameter Form you will make in Reports Builder.
   Examples\SimpleJSPPF\results\emprev_final.jsp	The source Web report that will become the target report for the Parameter Form.
   Examples\SimpleJSPPF\results\emprev_param.jsp	The final JSP-based Web report with a JSP Parameter Form.

   Note: If you completed the exercises in the Oracle Reports Tutorial, you can also use the emprevb_<your initials>.jsp file you finished at the end of Chapter 6.

39.1.2 Access to the sample schema

If you don't know if you have access to the sample schema provided with the Oracle9i database, contact your database administrator. You should have access to the "Human Resources" portion of the schema to complete this example.

39.2 Create a Parameter Form in HTML

The steps in this section will show you how to build a simple Parameter Form using plain HTML. You will then modify this HTML Parameter Form in Reports Builder so that you can call the Parameter Form from your JSP-based Web report.

If you don't want to create your own HTML file, you can open the sample HTML file we've provided in the Source directory, called paramform.html, then view the source code.

To create a simple Parameter Form in HTML:

1. In a text editor or HTML editor, create an HTML page that contains a form. The form should contain a list of values, a field, and a button. The code for this form can look something like the following:

   <form name="form1" method="post" action="">
       <select name="p_department" size="1">
         <option value="1">a</option>
       </select>
       <br>
       <input type="text" name="userid" value="hr/hr@db-connect">
   	<br>
       <input type="submit" name="Submit" value="Run Report">
   </form>
   
   

   Note: Although you can use the above code, you will need to change the userid value to reflect the connection information for your data source. You can also copy and paste the HTML code from the provided file, called simplejsppf_code.txt in the SimpleJSPPF/scripts directory, then modify it in a text or HTML editor.

2. Save the HTML file as paramform_<your initials>.html. When you display this HTML file in a Web browser, it should look similar to the following image:

Figure 39-3 Sample HTML Parameter Form

Text description of the illustration simplejsp_html.gif

39.3 Modify the HTML Parameter Form in Reports Builder

In this section, you will learn how to modify an HTML Parameter Form in Reports Builder to populate the list of values (LOV) you created with values from a data source. You will use JSP tags for Oracle Reports to enable the Parameter Form to access elements from a data model.

39.3.1 Create a data model manually for the Parameter Form

The steps in this section will show you how to create a simple data model for the Parameter Form.

To create a data model:

1. In Reports Builder, open the HTML file you created, paramform_<your initials>.html.

2. In the Object Navigator, double-click the icon next to the Data Model node to display the Data Model view.

3. In the Data Model view that displays, click the SQL Query tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.

4. In the SQL Query Statement field, enter the following SELECT statement:

   
   select department_name, department_id
   from departments
   order by department_name
   
   

   Note: You you can enter this query in any of the following ways: Copy and paste the code from the provided text file called simplejsppf_code.txt into the SQL Query Statement field. Click Query Builder to build the query without entering any code manually. Type the code in the SQL Query Statement field. Also note that if you have not installed the Pictures table into the sample schema, you will not be able to create this query.

5. Click Connect, then type the connection information for the "Human Resources" portion of the sample schema.

6. Click OK. Your data model should look like this:

Figure 39-4 Data Model view of the Parameter Form

Text description of the illustration simplejsp_dm.gif

7. Save the report as paramform_<your initials>.jsp.

39.3.2 Create a dynamic LOV in the Parameter Form

In this section, you will learn how to modify the Web source to pull data into the existing list of values (LOV) in your Parameter Form. This data will rely on the data model you created in the previous section. We will also examine the code to explain how each element operates.

To modify the LOV in the Parameter Form in Reports Builder:

1. Click the Web Source button in the toolbar to display the Web Source view.

2. In the Web Source view, look for the following code:

   <select name="p_department" size="1">
   <option value="1">a</option>
   </select>
   
   

   Note: In the above code, the LOV returns a static value. The display name is "a" and the value is "1".

Since the LOV is currently static, we need to change this HTML element to dynamically retrieve data based on our data model.

• In the Web Source view, modify the above code so that it looks like the following:

  <select name=" p_department">
  <rw:foreach id="fn" src="G_department_name"> 
  <option value="<rw:field id="f_deptId" src="department_id"/>"><rw:field 
  id="deptname1" src="department_name"/></option>
  </rw:foreach> 
  </select>
  
  

  Note: You can either type the code in manually or copy and paste it from the provided file called simplejsppf_code.txt.

  4. Save your report.

  Examine the JSP elements in the code:

  By using JSP tags for Oracle Reports in the above code, we retrieve data into the Parameter Form's LOV by basing the parameters on fields in the data model. Let's examine each element:

  • <option>: The display name of the LOV is replaced by the field department_name from the data model. When the user displays the Parameter Form, the department name will display in the list.

  • <rw:field>: This element accesses each element of the g_department_name group.

  • <rw:foreach>: This element iterates through the results based on the g_department_name group in the data model.

  • src: This parameter for the <rw:for each> element must match the group name of the data model.

  • <rw:field id>: This parameter can be any value, but it must be unique.

  By making these modifications to the code, we've replaced the return value attribute of the LOV with the field department_id, based on the data model we created in the previous section. If we now choose a department name from the list of values, its related department ID is returned. Note that the return value is not displayed.

  39.3.3 Run the Parameter Form report to the Web

  Now that we've modified and examined our Web source, let's view the Parameter Form in a Web browser.

  1. Click the Run to Web button in the toolbar.

     Note: if Netscape 7.0 is your default browser, the browser may not display. You can work around this bug by making a copy of the Netscape 7.0 executable, naming it netscape.exe; with this name, the browser will display as expected.

  2. The Parameter Form displays in your Web browser, and should look like the following:

  Figure 39-5 Parameter form with values

  

  Text description of the illustration simplejsp_pfv.gif

  Note: In the modified Parameter Form, notice how the list of values for the Department has changed from "a" to "Accounting."

  3. Click on the Department drop-down list and notice how the list is now populated with department names. Although you can click the Run Report button, nothing will happen because we have not yet defined an action for it.

  39.4 Set up the target report

  Now that you've set up the parameters, the next step is to set up the target report to accept the parameters. Then, we will define the action for your Parameter Form. When a user clicks the Run Report button, the target report will be run based on the Department and User ID parameters.

  The target report we use in this section is the sample report for the Oracle Reports Tutorial. If you completed the exercises in the Oracle Reports Tutorial and created "emprevb.jsp," you can use that report in this section. Otherwise, you can use the example file we've suppled, called emprev_final.jsp. We will not show the steps to build this report in this section.

  For more information on building the sample JSP-based Web report, refer to the Oracle Reports Tutorial.

  To set up the target report:

  1. In Reports Builder, open the file we've provided called emprev_final.jsp.

  2. In the Object Navigator, double-click the icon next to the Data Model node.

  3. In the Data Model view that displays, double-click the query (Q_1) to display the SQL Query Statement dialog box.

  4. Find the final line of the code:

     WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID) 
     AND EMPLOYEES.DEPARTMENT_ID = 100
     
     

  5. Change the second WHERE clause so that the last line looks like this:

     WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID) 
     AND EMPLOYEES.DEPARTMENT_ID = :P_DEPARTMENT
     
     

     Tip: The code we've changed is in bold text.

  6. Click OK.

  7. A note displays that indicates a bind parameter has been created. Click OK.

  8. Save your report as emprev_param_<your initials>.jsp.

  39.5 Deploy the JSP report and Parameter Form

  To deploy the JSP Parameter Form and the target JSP-based Web report, you must copy paramform_<your initials>.jsp and emprev_final_<your initials>.jsp to the deployment directory of your Application Server. For testing purposes, however, you can use the OC4J instance shipped with the Oracle Developer Suite. Once you've placed the target report in the desired directory, you can then modify the Parameter Form to point to the report location.

  For more information on deploying a JSP-based Web report, refer to the Oracle Application Server Reports Services Publishing Reports to the Web manual.

  To set up and deploy the JSP Parameter Form:

  1. Copy the JSP Parameter Form and the Web report (paramform_<your initials>.jsp and emprev_param_<your initials>.jsp) into the following directory:

     Oracle_Home\reports\j2ee\reports_ids\web
     
     

     Note: Oracle_Home is the directory in which Reports Builder is installed.

  2. In Reports Builder, open the file: Oracle_Home\reports\j2ee\reports_ids\web\paramform_<your initials>.jsp.

  3. In the Web Source view, modify the action for the form so that when the user clicks the Run Report button, the emprev_param_<your initials>.jsp report executes based on the selected parameters. The line of code should look like this:

     <form name="form1" method="post" action="/reports/emprev_param_<your 
     initials>.jsp">
     
     

     Note: In the above code, the action attribute assumes that the report is located in the directory we specified in the above steps. For more information on deploying JSP-based Web reports and JSP Parameter Forms, refer to the Oracle Application Server Reports Services Publishing Reports to the Web manual.

  4. Save your report.

  5. Start your OC4J instance.
     • On Windows, you can do either of the following:
       • From the Start menu, choose Programs>Oracle Developer Suite - oracle_home_name >Reports Developer>Reports Builder

       • From the command line, execute:

          <IDS_HOME>\j2ee\DevSuite\startinst.bat
         
         

     • On UNIX, start the shell script:

        <IDS_HOME>/j2ee/DevSuite/startinst.sh
       
       

       Tip: Once the containers for J2EE are initalized, the OC4J instance has been started.

     6. In a Web browser, type the URL for the Parameter Form:

        http://<machine name>:8888/reports/paramform_<your 
        initials>.jsp?userid=<userid>/<password>@<database name>
        
        

     In our example, we would use:

     http://mycomputer-pc:8888/reports/paramform.jsp?userid=hr/hr@orcl
     

     Note: The connect string you type in the URL is for the database you used to create the data model in Section 39.3.1, "Create a data model manually for the Parameter Form". For the purposes of this example, we've used plain text to pass the connect string. For information on using security, refer to the Securing Oracle Reports white paper, located on the Getting Started with Oracle Reports Web site on the Oracle Technology Network (http://otn.oracle.com/products/reports/). You can also find more information about security in the Oracle Application Server Reports Services Publishing Reports to the Web manual.

     7. When the Parameter Form displays, choose a department from the drop-down list. For example, choose Finance.

     8. In the Login ID field, type the connect string for the database schema that the emprev_param_<your initials>.jsp report uses. For example, hr/hr@orcl:

     Figure 39-6 JSP Parameter Form with selections

     

     Text description of the illustration simplejsp_rpf.gif

     9. Click the Run Report button in the toolbar.

     10. The employee salary report displays with a graph at the top, and should look like this:

     Figure 39-7 Sample employee report based on user parameters

     

     Text description of the illustration simplejsp_fin.gif

     Note: If you're not sure whether your report appears as it should, try using the files we've provided, paramform.jsp and emprev_param.jsp and follow all the steps in this section to deploy the Parameter Form and the Web report.

     39.6 Summary

     Congratulations! You have created a JSP Parameter Form for an existing Web report. You now know how to:

     • create a simple Parameter Form in HTML.

     • add a data model to a simple Parameter Form in Reports Builder.

     • modify an existing JSP-based Web report to accept user parameters.

     • test and deploy a JSP-based Web report with a JSP Parameter Form using OC4J.

     For more information on deploying JSP-based Web reports, refer to the Oracle Application Server Reports Services Publishing Reports to the Web manual. For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

     • From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

     • From Reports Builder, choose Help > Help Contents.

       Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.